package com.example.ggpicturegg.Mysql_com;

import android.util.Log;

import com.example.ggpicturegg.tool.CommonUtils;
import com.mysql.jdbc.PreparedStatement;

import java.util.ArrayList;
import java.util.List;

/*
        用户数据库操作类
        实现用户的 CRUD 操作
 */
public class UserDao extends DbOpenHelper{

    // 查询 所有的用户 信息
    public List<Userinfo> getAllUserList(){
        List<Userinfo> list= new ArrayList<>();
        try{
            getConnection();
            String sql="select * from userinfo";
            pStmt = (PreparedStatement) conn.prepareStatement(sql);
            rs = pStmt.executeQuery();
            if(rs.next()){
                Userinfo item = new Userinfo();
                item.setId(rs.getInt("id"));
                item.setUname(rs.getString("uname"));
                item.setUpass(rs.getString("upass"));

                list.add(item);
            }

        }catch (Exception e){
            Log.d("getUserByUnameAndUpass", "error ");
            e.printStackTrace();
        }finally {
            clossAll();  //关闭所有连接
        }
        return list;
    }

    // 按照用户名和密码查询用户信息
    public Userinfo getUserByUnameAndUpass(String uname , String upass){
        Userinfo item = null;
        try{
            getConnection();
            String sql="select * from userinfo where name=? and passwd=?";
            pStmt = (PreparedStatement) conn.prepareStatement(sql);
            pStmt.setString(1,uname);
            pStmt.setString(2,upass);
            rs = pStmt.executeQuery();
            if(rs.next()){
                item = new Userinfo();
                item.setId(rs.getInt("id"));
                item.setUname(uname);
                item.setUpass(upass);
            }

        }catch (Exception e){
            Log.d("getUserByUnameAndUpass", "error ");
            e.printStackTrace();
        }finally {
            clossAll();  //关闭所有连接
        }
        return item;
    }

    // 添加用户信息
    public int addUser(Userinfo item){
        int iRow = 0;
        try{
            getConnection();
            String sql="insert into userinfo(name,passwd) values(?,?)";
            pStmt = (PreparedStatement) conn.prepareStatement(sql);
            pStmt.setString(1,item.getUname());
            pStmt.setString(2,item.getUpass());
            iRow = pStmt.executeUpdate();
        }catch (Exception e){
            Log.d("addUser", "error ");
            e.printStackTrace();
        }finally {
            clossAll();  //关闭所有连接
        }
        return iRow;    // 返回 ： 影响的行数
    }




    // 编辑用户信息
    public int editUser(Userinfo item){
        int iRow = 0;
        try{
            getConnection();
            String sql="update userinfo set uname=?,upass=? where id=?";
            pStmt = (PreparedStatement) conn.prepareStatement(sql);
            pStmt.setString(1,item.getUname());
            pStmt.setString(2,item.getUpass());
            pStmt.setInt(3,item.getId());
            iRow = pStmt.executeUpdate();
        }catch (Exception e){
            Log.d("editUser", "error ");
            e.printStackTrace();
        }finally {
            clossAll();  //关闭所有连接
        }
        return iRow;    // 返回 ： 影响的行数
    }

    // 编辑用户信息
    // 根据用户的id删除
    public int delUser(int id){
        int iRow = 0;
        try{
            getConnection();
            String sql="delete from userinfo where id=?";
            pStmt = (PreparedStatement) conn.prepareStatement(sql);
            pStmt.setInt(1,id);
            iRow = pStmt.executeUpdate();
        }catch (Exception e){
            Log.d("delUser", "error ");
            e.printStackTrace();
        }finally {
            clossAll();  //关闭所有连接
        }
        return iRow;    // 返回 ： 影响的行数
    }

    //获取用户的个数
    public  int getUserNumber(){

        int count = 0 ; // 用户的数量
        try{
            getConnection(); //尝试连接
            String sql = "select count(1) as sl from userinfo";
            pStmt = (PreparedStatement) conn.prepareStatement(sql);
            rs = pStmt.executeQuery();
            while(rs.next()){
                count = rs.getInt("sl");
            }

        }catch (Exception e){
            Log.d("MysqlHelp", "ERROR");
            e.printStackTrace();

        }

        return count;
    }

}
